package com.kma.ncpractice2013.reports;

import com.kma.ncpractice2013.dao.Connector;
import com.kma.ncpractice2013.dao.HotelDAO;
import com.kma.ncpractice2013.dao.OrderDAO;
import com.kma.ncpractice2013.model.Hotel;
import com.kma.ncpractice2013.model.Order;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;

import javax.activation.DataSource;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.Calendar;
import java.util.List;


/**
 * User: Viktor
 * Date: 10/13/13
 */

public class POIServlet extends HttpServlet
{
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {


	    String rep_type = request.getParameter("report_type");
	    Date startDate = Date.valueOf(request.getParameter("startdate"));
	    Date endDate = Date.valueOf(request.getParameter("enddate"));
	    XSSFWorkbook workbook = null;
	    if(rep_type.equals("top_price"))
	    {
			workbook = get_top_price(startDate, endDate);
	    }
	    else
	    if(rep_type.equals("top_dest"))
	    {
		    try
		    {
			    workbook = get_top_dest(startDate, endDate);
		    } catch (SQLException e)
		    {
			    response.sendRedirect("/error.jsp?err=sqlerr");
		    }
	    }
	    Date d = new Date(System.currentTimeMillis());
	    Calendar calendar = Calendar.getInstance();
	    calendar.setTime(d);



		ServletOutputStream outputStream = response.getOutputStream();
	    response.setHeader("Content-Disposition", "attachment; filename="+calendar.get(Calendar.YEAR)+"-"
			                                              +calendar.get(Calendar.MONTH)+"-"+
			                                              calendar.get(Calendar.DAY_OF_MONTH)+"-"+rep_type+".xlsx");
	                       response.setContentType("application/vnd.ms-excel");
	    workbook.write(outputStream);
    }

	private XSSFWorkbook get_top_price(Date startDate, Date endDate)
	{

		OrderDAO orderDAO = new OrderDAO();
		List<Order> orderList = orderDAO.getAllOrderedTimeframe("price", startDate, endDate);

		HotelDAO hotelDAO = new HotelDAO();

		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("sheet1");
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.INDIGO.getIndex());
		CellStyle priceStyle = workbook.createCellStyle();
		priceStyle.cloneStyleFrom(style);
		priceStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
		Font pricefont = workbook.createFont();
		pricefont.setItalic(true);
		priceStyle.setFont(pricefont);
		Font font = workbook.createFont();
		font.setFontName("Verdana");
		XSSFRow row;
		XSSFCell cell;
		Hotel loc;
		CellStyle dateStyle = workbook.createCellStyle();
		XSSFDataFormat df = workbook.createDataFormat();

		dateStyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
		dateStyle.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
		dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		dateStyle.setBorderRight(CellStyle.BORDER_THIN);
		dateStyle.setRightBorderColor(IndexedColors.INDIGO.getIndex());
		row = sheet.createRow(sheet.getLastRowNum()+1);
		CellStyle header = workbook.createCellStyle();
		header.setFillForegroundColor(IndexedColors.LIME.getIndex());
		Font font1 = workbook.createFont();
		font1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		header.setFont(font1);
		header.setBorderBottom(CellStyle.BORDER_MEDIUM);
		header.setBottomBorderColor(IndexedColors.BLACK.getIndex());

		createFirstStyledCell(row, "Country", header);
		createStyledCell(row, "City", header);
		createStyledCell(row, "Hotel", header);
		createStyledCell(row, "Order date", header);
		createStyledCell(row, "Checkin", header);
		createStyledCell(row, "Checkout", header);
		createStyledCell(row, "Adults", header);
		createStyledCell(row, "Children", header);
		createStyledCell(row, "Rooms", header);
		createStyledCell(row, "Price", header);
		createStyledCell(row, "Stars", header);

		for (Order o : orderList)
		{

			loc = hotelDAO.getByHotelId(o.getLocationId());
			row = sheet.createRow(sheet.getLastRowNum()+1);


			createFirstStyledCell(row, loc.getCountry(), style);
			createStyledCell(row, loc.getCity(), style);
			createStyledCell(row, loc.getName(), style);
			createStyledCell(row, o.getOrderDate(), dateStyle);
			createStyledCell(row, o.getCheckInDate(), dateStyle);
			createStyledCell(row, o.getCheckOutDate(), dateStyle);
			createStyledCell(row, o.getAdults(), style);
			createStyledCell(row, o.getChildren(), style);
			createStyledCell(row, o.getRooms(), style);
			createStyledCell(row, o.getPrice()+"$", priceStyle);
			createStyledCell(row, o.getHotelStars(), style);


		}
		for(int i = 0; i <20; i++)
			sheet.autoSizeColumn(i);
		return workbook;
	}


	private XSSFWorkbook get_top_dest(Date startDate, Date endDate) throws SQLException
	{

		javax.sql.DataSource ds = Connector.getDataSource();
		String sql = "SELECT hotels.name, hotel_id AS ids, OrderCount FROM (SELECT ORDERS.HOTEL_ID AS ids, COUNT(orders.id)" +
				             " AS OrderCOunt FROM Orders where orders.date_of_order > ? and orders.date_of_order< ? GROUP BY orders.hotel_id )" +
				             " JOIN hotels ON hotels.hotel_id = ids ORDER BY OrderCount DESC";

		ResultSet rs;

		Connection conn = ds.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setDate(1, startDate);
		ps.setDate(2, endDate);
		rs = ps.executeQuery();
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("sheet1");
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.INDIGO.getIndex());
		CellStyle countStyle = workbook.createCellStyle();
		countStyle.cloneStyleFrom(style);

		countStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
		Font countfont = workbook.createFont();
		countfont.setItalic(true);
		countStyle.setFont(countfont);
		Font font = workbook.createFont();
		font.setFontName("Verdana");
		XSSFRow row;



		row = sheet.createRow(sheet.getLastRowNum() + 1);
		CellStyle header = workbook.createCellStyle();
		header.setFillForegroundColor(IndexedColors.LIME.getIndex());
		Font font1 = workbook.createFont();
		font1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		header.setFont(font1);
		header.setBorderBottom(CellStyle.BORDER_MEDIUM);
		header.setBottomBorderColor(IndexedColors.BLACK.getIndex());

		createFirstStyledCell(row, "Hotel name", header);
		createStyledCell(row, "Hotel id", header);
		createStyledCell(row, "Order count", header);



			while (rs.next())
			{


				row = sheet.createRow(sheet.getLastRowNum() + 1);


				createFirstStyledCell(row, rs.getString("name"), style);
				createStyledCell(row, rs.getInt("ids"), style);
				createStyledCell(row, rs.getInt("OrderCount"), countStyle);



			}
			for (int i = 0; i < 5; i++)
				sheet.autoSizeColumn(i);


		return workbook;
	}

	private void createFirstStyledCell(XSSFRow row, String content, CellStyle cellStyle)
	{
		XSSFCell cell;
		cell = row.createCell(row.getLastCellNum()+1);
		cell.setCellValue(content);
		cell.setCellStyle(cellStyle);
	}
	private void createStyledCell(XSSFRow row, String content, CellStyle cellStyle)
	{
		XSSFCell cell;
		cell = row.createCell(row.getLastCellNum());
		cell.setCellValue(content);
		cell.setCellStyle(cellStyle);
	}
	private void createStyledCell(XSSFRow row, java.util.Date content, CellStyle cellStyle)
	{
		XSSFCell cell;
		cell = row.createCell(row.getLastCellNum());
		cell.setCellValue(content);
		cell.setCellStyle(cellStyle);
	}
	private void createStyledCell(XSSFRow row, int content, CellStyle cellStyle)
	{
		XSSFCell cell;
		cell = row.createCell(row.getLastCellNum());
		cell.setCellValue(content);
		cell.setCellStyle(cellStyle);
	}
	private void createStyledCell(XSSFRow row, double content, CellStyle cellStyle)
	{
		XSSFCell cell;
		cell = row.createCell(row.getLastCellNum());
		cell.setCellValue(content);
		cell.setCellStyle(cellStyle);
	}
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
		doPost(request, response);


    }


}
